<?php

/**
 * MembersDAO
 *
 */
class MembersDAO
{
    public function findName($name)
    {
        $db = Yii::app()->db;
        $active = Constants::STATUS_ACTIVE;

        $sql = 'SELECT m.member_id,m.name,m.nickname,m.password,m.mac_address,m.status AS member_status,m.parent_member_id, '
                . 'a.account_id, a.site_id, a.status, a.ad_status, a.grid_status, s.sitename '
                . 'FROM members AS m '
                . 'INNER JOIN accounts AS a ON m.member_id = a.member_id '
                . 'INNER JOIN sites AS s ON a.site_id = s.site_id '
                . 'WHERE m.status = :active '
                . 'AND m.nickname = :nickname '
                . 'ORDER BY image_id ASC';

        $command = $db->createCommand($sql);
        $command->bindParam(':active', $active);
        $command->bindParam(':nickname', $name);
        $result = $command->queryAll();

        if ($result) {
            return $result;
        }

        return NULL;
    }

    public function findByNameAndMac($name, $mac)
    {
        $db = Yii::app()->db;
        $active = Constants::STATUS_ACTIVE;

        $sql = 'SELECT m.member_id,m.name,m.nickname,m.password,m.mac_address,m.status AS member_status,m.parent_member_id '
             . 'FROM members AS m '
             . 'WHERE m.status = :active '
             . 'AND m.nickname = :nickname '
             . 'AND m.mac_address = :mac ';

        $command = $db->createCommand($sql);
        $command->bindParam(':active', $active);
        $command->bindParam(':nickname', $name);
        $command->bindParam(':mac', $mac);
        $result = $command->queryAll();
    
        if ($result) {
            return $result[0];
        }
    
        return NULL;
    }

    public function findAccountById($id)
    {
        $db = Yii::app()->db;

        $sql = 'SELECT account_id, site_id '
             . 'FROM accounts '
             . 'WHERE member_id = :member ';

        $command = $db->createCommand($sql);
        $command->bindParam(':member', $id);
        $result = $command->queryAll();

        if ($result) {
            return $result;
        }
    
        return NULL;
    }
}